package com.pks.datacenter.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.pks.datacenter.dto.DbTableColumnInfo;
import com.pks.datacenter.entity.basic.DbField;

/**
 * 
 * @author pks
 * @version 2019年9月6日 
 */
public class SUtilDb {

	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static final int dbtype_mysql = 1;
	public static final int dbtype_sqlserver = 2;
	public static final int dbtype_oracle = 3;
	
	public static void executeSql(Integer dbtype,String db_ip,Integer db_port,String db_name,String db_account,String db_password,String sql) throws Exception{
		Statement stmt = null;
		Connection conn = null;
		try {
			DriverManager.setLoginTimeout(1);
			if(dbtype_mysql == dbtype){
				Class.forName("com.mysql.cj.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://"+db_ip+":"+db_port+"/"+db_name + "?serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false", db_account,db_password);
			}else if(dbtype_sqlserver == dbtype){
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				conn = DriverManager.getConnection("jdbc:sqlserver://"+db_ip+":"+db_port+";DatabaseName="+db_name,db_account,db_password);
			}else if(dbtype_oracle == dbtype){
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection("jdbc:oracle:thin:@"+db_ip+":"+db_port+":"+db_name, db_account, db_password);
			}
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static List<JSONObject> queryList(Integer dbtype,String db_ip,Integer db_port,String db_name,String db_account,String db_password,String sql){
		List<JSONObject> list = null;
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			DriverManager.setLoginTimeout(1);
			if(dbtype_mysql == dbtype){
				Class.forName("com.mysql.cj.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://"+db_ip+":"+db_port+"/"+db_name+ "?useSSL=false&serverTimezone=UTC", db_account,db_password);
//				conn = DriverManager.getConnection("jdbc:mysql://"+db_ip+":"+db_port+"/"+db_name+ "?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=Asia/Shanghai", db_account,db_password);
//				conn = DriverManager.getConnection("jdbc:mysql://"+db_ip+":"+db_port+"/"+db_name, db_account,db_password);
			}else if(dbtype_sqlserver == dbtype){
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				conn = DriverManager.getConnection("jdbc:sqlserver://"+db_ip+":"+db_port+";DatabaseName="+db_name,db_account,db_password);
			}else if(dbtype_oracle == dbtype){
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection("jdbc:oracle:thin:@"+db_ip+":"+db_port+":"+db_name, db_account, db_password);
			}
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			list = resultSetToJsonObjectForMySQL(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	

	public static void insertDatas(Integer dbtype,String db_ip,Integer db_port,String db_name,String db_account,String db_password,String table_name, List<DbField> fields,List<JSONObject> datas) {
		
		Statement stmt = null;
		Connection conn = null;
		try {
			DriverManager.setLoginTimeout(1);
			if(dbtype_mysql == dbtype){
				Class.forName("com.mysql.cj.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://"+db_ip+":"+db_port+"/"+db_name+"?serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false", db_account,db_password);
			}else if(dbtype_sqlserver == dbtype){
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				conn = DriverManager.getConnection("jdbc:sqlserver://"+db_ip+":"+db_port+";DatabaseName="+db_name,db_account,db_password);
			}else if(dbtype_oracle == dbtype){
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection("jdbc:oracle:thin:@"+db_ip+":"+db_port+":"+db_name, db_account, db_password);
			}
			stmt = conn.createStatement();
//			stmt.execute("delete from t_user where time = '"+timeStr+"'");
			StringBuffer sb1 = new StringBuffer("insert into `"+table_name+"` (");
			for (int i = 0; i < fields.size(); i++) {
				DbField field = fields.get(i);
				if(i>0) sb1.append(",");
				sb1.append("`"+field.getEnName()+"`");
			}
			sb1.append(")");
			StringBuffer sb2 = new StringBuffer();
			if(datas!=null && datas.size()>0){
				int start = 0;
				int size = 1000;
				while (start<datas.size()) {
					int toindex = (start+size)<datas.size()?(start+size):datas.size();
					sb2 = new StringBuffer(" values ");
					List<JSONObject> temp = datas.subList(start,toindex);
					for (int i = 0; i < temp.size(); i++) {
						JSONObject jsonObject = temp.get(i);
						if(i>0) sb2.append(",");
						sb2.append("(");
						for (int j = 0; j < fields.size(); j++) {
							DbField field = fields.get(j);
							if(j>0) sb2.append(",");
							if(jsonObject.containsKey(field.getEnName())){
								if(DbField.type_number == field.getType()){
									sb2.append(jsonObject.getString(field.getEnName()));
								}else if(DbField.type_date == field.getType()){
									sb2.append("'"+jsonObject.getString(field.getEnName())+"'");
								}else{
									sb2.append("'"+jsonObject.getString(field.getEnName())+"'");
								}
							}else{
								sb2.append("null");
							}
						}
						sb2.append(")");
					}
					stmt.execute(sb1.toString() + sb2.toString());
					start=toindex;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	public static List<JSONObject> resultSetToJsonObjectForMySQL(ResultSet rs) throws Exception {

		List<JSONObject> result = new ArrayList<JSONObject>();

		ResultSetMetaData metaData = rs.getMetaData();
		int count = metaData.getColumnCount();
		while (rs.next()) {
			JSONObject json = new JSONObject();
			for (int i = 1; i <= count; i++) {
				String filedName = metaData.getColumnName(i);
				if (null == rs.getObject(i)) {
//						json.fluentPut(filedName, null);
					continue;
				}
				switch (metaData.getColumnType(i)) {
				case Types.DATE:
				case Types.TIME:
				case Types.TIMESTAMP:
					json.fluentPut(filedName,sdf.format(new Date(rs.getTimestamp(i).getTime())));
					break;
				case Types.BIGINT:
					json.fluentPut(filedName, rs.getBigDecimal(i).toBigInteger());
					break;
				case Types.DECIMAL:
					json.fluentPut(filedName, rs.getInt(i));
					break;
				case Types.INTEGER:
					json.fluentPut(filedName, rs.getLong(i));
					break;
				case Types.DOUBLE:
					json.fluentPut(filedName, rs.getDouble(i));
					break;
				case Types.FLOAT:
					json.fluentPut(filedName, rs.getFloat(i));
					break;
				default:
					json.fluentPut(filedName, rs.getString(i).trim());
					break;
				}
			}
			result.add(json);
		}
		return result;
	}
	
	/**
	 * 
	 * @param dbtype
	 * @param db_ip
	 * @param db_port
	 * @param db_name
	 * @param db_account
	 * @param db_password
	 * @param table_name
	 * @return List<JSONObject>   JSONObject: {TABLE_NAME,TABLE_ROWS}
	 */
	public static List<JSONObject> queryDbTableList(Integer dbtype,String db_ip,Integer db_port,String db_name,String db_account,String db_password){
		List<JSONObject> list = null;
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		String sql = "";
		try {
			DriverManager.setLoginTimeout(1);
			if(dbtype_mysql == dbtype){
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://"+db_ip+":"+db_port+"/"+db_name+"?serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false", db_account,db_password);
				sql = String.format("select TABLE_NAME,TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA = '%s' ",db_name);
			}else if(dbtype_sqlserver == dbtype){
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				conn = DriverManager.getConnection("jdbc:sqlserver://"+db_ip+":"+db_port+";DatabaseName="+db_name,db_account,db_password);
			}else if(dbtype_oracle == dbtype){
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection("jdbc:oracle:thin:@"+db_ip+":"+db_port+":"+db_name, db_account, db_password);
				sql = String.format("");
			}
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			list = resultSetToJsonObjectForMySQL(rs);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	
	public static List<DbTableColumnInfo> queryTableColumnList(Integer dbtype,String db_ip,Integer db_port,String db_name,String db_account,String db_password,String table_name){
		List<DbTableColumnInfo> result = null;
		List<JSONObject> list = null;
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		String sql = "";
		try {
			DriverManager.setLoginTimeout(1);
			if(dbtype_mysql == dbtype){
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://"+db_ip+":"+db_port+"/"+db_name+"?serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false", db_account,db_password);
				sql = String.format("select COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY, COLUMN_COMMENT from information_schema.columns where table_schema ='%s' and table_name = '%s' ;",db_name,table_name);
			}else if(dbtype_sqlserver == dbtype){
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				conn = DriverManager.getConnection("jdbc:sqlserver://"+db_ip+":"+db_port+";DatabaseName="+db_name,db_account,db_password);
			}else if(dbtype_oracle == dbtype){
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection("jdbc:oracle:thin:@"+db_ip+":"+db_port+":"+db_name, db_account, db_password);
				sql = String.format("select t.TABLE_NAME as tableName,t.COLUMN_NAME as columnName,c.comments as columnComment,t.NULLABLE as nummable,t.DATA_TYPE as dataType,t.CHAR_LENGTH as strLength,t.DATA_SCALE as numBit from user_tab_columns t,user_col_comments c where t.TABLE_NAME = c.TABLE_NAME and t.COLUMN_NAME = c.COLUMN_NAME AND t.TABLE_NAME = '%s' order by t.TABLE_NAME,t.COLUMN_ID", table_name);
			}
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			list = resultSetToJsonObjectForMySQL(rs);
			result = new ArrayList<DbTableColumnInfo>();
			if (list != null) {
				for (JSONObject jsonObject : list) {
					DbTableColumnInfo info =  JSON.parseObject(jsonObject.toJSONString(), new TypeReference<DbTableColumnInfo>() {});
					result.add(info);
				}
			}
			return result;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

}
